package com.banfeiguanli.springboot.mapper;

import com.banfeiguanli.springboot.entity.ExpenseApply;
import com.banfeiguanli.springboot.entity.PaymentProject;
import org.apache.ibatis.annotations.*;

import java.sql.Timestamp;
import java.util.List;

@Mapper
public interface PaymentProjectMapper {
    //获取班级缴费项目(班级管理员)
    @Select("select * from jfxm " +
            "where classID = (select UI_ClassID from userinfo where UserID = #{userID}) " +
            "and payProjTitle like concat('%',#{searchTitle}, '%') " +
            "order by releaseTime desc " +
            "limit #{pageNumber}, #{pageSize}")
    List<PaymentProject> getClassPaymentProject(@Param(value = "userID") int userID,
                                                @Param(value = "pageNumber") Integer pageNumber,
                                                @Param(value = "pageSize") Integer pageSize,
                                                @Param(value = "searchTitle") String searchTitle);

    //获取班级缴费项目(用户)
    @Select("select payProjID,classID,className,releaseUserID,releaseUser,payProjTitle,payProjBrief,payProjAmount, " +
            "releaseTime, deadLine, isDelete " +
            "from (select * " +
            "from (select * " +
            "from jfxm " +
            "where classID = (select UI_ClassID from userinfo where UserID = #{userID}) " +
            "and payProjTitle like concat('%', #{searchTitle}, '%')) as AllClassPay " +
            "left outer join (select * " +
            "from paymentrecord " +
            "where PR_UserID = #{userID}) as pay " +
            "on AllClassPay.payProjID = pay.PR_PayProjID " +
            "having PayRecTime is null) notPay " +
            "where deadLine > current_timestamp " +
            "and isDelete = '否' " +
            "order by releaseTime " +
            "limit #{pageNumber}, #{pageSize}")
    List<PaymentProject> getClassPaymentProjectForUser(@Param(value = "userID") int userID,
                                                       @Param(value = "pageNumber") Integer pageNumber,
                                                       @Param(value = "pageSize") Integer pageSize,
                                                       @Param(value = "searchTitle") String searchTitle);

    //获取缴费项目总数
    @Select("select count(*) " +
            "from jfxm " +
            "where classID = (select UI_ClassID from userinfo where UserID = #{userID}) " +
            "and payProjTitle like concat('%',#{searchTitle}, '%')")
    Integer countPaymentProjectTotal(@Param(value = "userID") int userID,
                                     @Param(value = "searchTitle") String searchTitle);

    //获取缴费项目总数(用户)
    @Select("select count(*)" +
            "from (select * " +
            "from (select * " +
            "from jfxm " +
            "where classID = (select UI_ClassID from userinfo where UserID = #{userID}) " +
            "and payProjTitle like concat('%', #{searchTitle}, '%')) as AllClassPay " +
            "left outer join (select * " +
            "from paymentrecord " +
            "where PR_UserID = #{userID}) pay " +
            "on AllClassPay.payProjID = pay.PR_PayProjID " +
            "having PayRecTime is null) notPay " +
            "where deadLine > current_timestamp ")
    Integer countPaymentProjectTotalForUser(@Param(value = "userID") int userID,
                                            @Param(value = "searchTitle") String searchTitle);

    @Select("select * from jfxm where PayProjID = #{payProjID}")
    List<PaymentProject> getPaymentProjectInfo(@Param(value = "payProjID") int payProjID);

    //删除缴费项目(假删)
    @Update("update paymentproject set IsDelete = '是' where PayProjID = #{payProjID}")
    Integer deleteProject(@Param(value = "payProjID") int payProjID);

    //创建缴费项目
    @Insert("insert into paymentproject(PP_ClassAdminID, PayProjTitle, PayProjBrief, PayProjAmount, ReleaseTime, Deadline)" +
            "values (#{classAdminID}, #{payProjTitle}, #{payProjBrief}, #{payProjAmount}, #{releaseTime}, #{deadLine})")
    Integer createPaymentProject(@Param(value = "classAdminID") int classAdminID,
                                 @Param(value = "payProjTitle") String payProjTitle,
                                 @Param(value = "payProjBrief") String payProjBrief,
                                 @Param(value = "payProjAmount") float payProjAmount,
                                 @Param(value = "releaseTime") Timestamp releaseTime,
                                 @Param(value = "deadLine") Timestamp deadLine);

    @Select("select ClassExBala " +
            "from class " +
            "where ClassID = " +
            "(select UI_ClassID from userinfo where UserID = #{userID})")
    Float getClassExpense(Integer userID);

    //缴纳班费
    @Insert("insert into paymentrecord(PR_UserID, PR_PayProjID, BeforeClassExp, PayRecTime)" +
            "values (#{userID}, #{paymentProjectID}, #{classExp}, current_timestamp)")
    Integer pay(@Param(value = "userID") int userID,
                @Param(value = "paymentProjectID") Integer paymentProjectID,
                @Param(value = "classExp") float classExp);

    //更新班费余额
    @Update("update class set ClassExBala = ClassExBala + (" +
            "select PayProjAmount " +
            "from paymentproject " +
            "where PayProjID = #{paymentProjectID})" +
            "where ClassID = (" +
            "select UI_ClassID " +
            "from userinfo " +
            "where UserID = #{userID})")
    Integer updateClassExpense(@Param(value = "userID") int userID,
                               @Param(value = "paymentProjectID") Integer paymentProjectID);


    //查询所有班费申请（班级管理员）
    @Select("select expApplyID, applyAmount, applyTime, auditTime, applyReason, status, userinfo.UserID, userinfo.UserName as applyUserName, " +
            "classAdminID, bjgly.UserName as auditUserName " +
            "from expenseapply,userinfo, bjgly " +
            "where EA_UserID = userinfo.UserID " +
            "and EA_ClassAdminID = ClassAdminID " +
            "and EA_ClassAdminID in ( " +
            "select ClassAdminID from classadmin,userinfo " +
            "where CA_ClassID = UI_ClassID " +
            "and UserID = #{userID}) " +
            "and ApplyReason like concat('%', #{searchExpenseApplyReason}, '%' ) " +
            "order by ApplyTime desc " +
            "limit #{applyMessagePageNumber}, #{applyMessagePageSize}")
    List<ExpenseApply> getExpenseApply(@Param(value = "userID") int userID,
                                       @Param(value = "applyMessagePageNumber") Integer applyMessagePageNumber,
                                       @Param(value = "applyMessagePageSize") Integer applyMessagePageSize,
                                       @Param(value = "searchExpenseApplyReason") String searchExpenseApplyReason);


    //
    @Select("select count(*)" +
            " from expenseapply" +
            " where  EA_ClassAdminID" +
            " in (select ClassAdminID from classadmin,userinfo" +
            " where CA_ClassID = UI_ClassID and UserID = #{userID})" +
            "and ApplyReason like concat('%', #{searchExpenseApplyReason}, '%' )")
    Integer getExpenseApplyTotal(@Param(value = "userID") int userID, @Param(value = "searchExpenseApplyReason") String searchExpenseApplyReason);


    //拒绝班费申请
    @Update("update expenseapply " +
            "set status = '不同意'," +
            "EA_ClassAdminID = #{classAdminID}, " +
            "AuditTime = CURRENT_TIMESTAMP " +
            "where ExpApplyID = #{expApplyID}")
    Integer refuseExpenseApply(@Param(value = "classAdminID") int classAdminID, @Param(value = "expApplyID") int expApplyID);


    //管理员用户对应的班级管理员ID
    @Select("select ClassAdminID " +
            "from classadmin " +
            "where CA_UserID = #{userID} " +
            "and CA_ClassID = (select UI_ClassID from userinfo where UserID = #{userID})")
    int getClassAdminID(@Param(value = "userID") int userID);


    //同意班费申请
    @Update("update expenseapply " +
            "set status = '同意', " +
            "EA_ClassAdminID = #{classAdminID}, " +
            "BeforeClassExp = #{balance}, " +
            "AuditTime = CURRENT_TIMESTAMP " +
            "where ExpApplyID = #{expApplyID}")
    Integer approveExpenseApply(@Param(value = "classAdminID") int classAdminID,
                                @Param(value = "expApplyID") int expApplyID,
                                @Param(value = "balance") float balance);
    //更新班费余额
    @Update("update class " +
            "set ClassExBala = ClassExBala + (select ApplyAmount from expenseapply where ExpApplyID = #{expApplyID}) " +
            "where ClassID = (select UI_ClassID from userinfo where UserID = #{userID} )")
    Integer updateClassExpenseForApply(@Param("userID") int userID, @Param("expApplyID") int expApplyID);
    //更新班费余额

}
